﻿-- =============================================
-- Script Template
-- =============================================


CREATE procedure P_TrendChart_22X5_HMFB --22选5号码分布图

AS

set nocount on

Create table #LotteryNumberList22X5(id int identity(1,1),Isuse varchar(20),LotteryNumber varchar(100),
		B_1 int,B_2 int,B_3 int,B_4 int,B_5 int,B_6 int,B_7 int,B_8 int,B_9 int,B_10 int,
		B_11 int,B_12 int,B_13 int,B_14 int,B_15 int,B_16 int,B_17 int,B_18 int,B_19 int,B_20 int,
		B_21 int,B_22 int,J int,O int, D int,X int,H_Z int,Y_0 INT,Y_1 INT,Y_2 INT)

declare @cur cursor 
declare @Isuse varchar(20), @WinLotterNumber varchar(100), @id int 

declare @B_1 int,@B_2 int,@B_3 int,@B_4 int,@B_5 int,@B_6 int,@B_7 int,@B_8 int,@B_9 int,@B_10 int,
	@B_11 int,@B_12 int,@B_13 int,@B_14 int,@B_15 int,@B_16 int,@B_17 int,@B_18 int,@B_19 int,@B_20 int,
	@B_21 int,@B_22 int,@J int,@O int, @D int,@X int,@Y_0 INT,@Y_1 INT,@Y_2 INT

declare @W int,@Q int,@B int,@S int,@G int,@H_Z int

Create table #tb(id int,PlayNo varchar(20),WinLotteryNumber varchar(100))


insert into #tb select top 100 Id,PlayNo,WinLotteryNumber from pm_plays where WinLotteryNumber <> '' and LotteryID =3 order by PlayNo desc


set @B_1 = 0 set @B_2 = 0 set @B_3 = 0 set @B_4 = 0 set @B_5 = 0 set @B_6 = 0  set @B_7 = 0 set @B_8 = 0 set @B_9 = 0
set @B_10 = 0 set @B_11 = 0 set @B_12 = 0 set @B_13 = 0 set @B_14 = 0 set @B_15 = 0 set @B_16 = 0  set @B_17 = 0 set @B_18 = 0 
set @B_19 = 0 set @B_20 = 0 set @B_21 = 0 set @B_22 = 0 

set @cur = cursor scroll for select PlayNo,WinLotteryNumber from #tb order by PlayNo asc

open @cur

fetch first from @cur into @Isuse, @WinLotterNumber

while @@fetch_status=0
begin

set @W = Substring(@WinLotterNumber,1,2) 
set @Q = Substring(@WinLotterNumber,4,2) 
set @B = Substring(@WinLotterNumber,7,2)
set @S = Substring(@WinLotterNumber,10,2) 
set @G = Substring(@WinLotterNumber,13,2)

--select @Isuse, @WinLotterNumber, @W, @Q, @B, @S, @G

SET @J=0 SET @O = 0 SET @D=0 SET @X=0 SET @H_Z =0 SET @Y_0 =0 SET @Y_1 =0 SET @Y_2 =0

set @B_1 =@B_1 + 1 set @B_2 = @B_2 + 1 set @B_3 = @B_3 + 1 set @B_4 = @B_4 + 1 
set @B_5 = @B_5 + 1 set @B_6 = @B_6 + 1  set @B_7 = @B_7 + 1 set @B_8 = @B_8 + 1 set @B_9 = @B_9 + 1 
set @B_10 = @B_10 + 1 set @B_11 =@B_11 + 1 set @B_12 = @B_12 + 1 set @B_13 = @B_13 + 1 set @B_14 = @B_14 + 1 
set @B_15 = @B_15 + 1 set @B_16 = @B_16 + 1  set @B_17 = @B_17 + 1 set @B_18 = @B_18 + 1 set @B_19 = @B_19 + 1 
set @B_20 = @B_20 + 1 set @B_21 =@B_21 + 1 set @B_22 = @B_22 + 1

IF(@W = 01 OR @Q=01 OR @B=01 OR @S=01 OR @G=01)
BEGIN
	SET @B_1 = 0
END

IF(@W = 02 OR @Q=02 OR @B=02 OR @S=02 OR @G=02)
BEGIN
	SET @B_2 = 0
END

IF(@W = 03 OR @Q=03 OR @B=03 OR @S=03 OR @G=03)
BEGIN
	SET @B_3 = 0
END

IF(@W = 04 OR @Q=04 OR @B=04 OR @S=04 OR @G=04)
BEGIN
	SET @B_4 = 0
END

IF(@W = 05 OR @Q=05 OR @B=05 OR @S=05 OR @G=05)
BEGIN
	SET @B_5 = 0
END

IF(@W = 06 OR @Q=06 OR @B=06 OR @S=06 OR @G=06)
BEGIN
	SET @B_6 = 0
END

IF(@W = 07 OR @Q=07 OR @B=07 OR @S=07 OR @G=07)
BEGIN
	SET @B_7 = 0
END

IF(@W = 08 OR @Q=08 OR @B=08 OR @S=08 OR @G=08)
BEGIN
	SET @B_8 = 0
END

IF(@W = 09 OR @Q=09 OR @B=09 OR @S=09 OR @G=09)
BEGIN
	SET @B_9 = 0
END

IF(@W = 10 OR @Q=10 OR @B=10 OR @S=10 OR @G=10)
BEGIN
	SET @B_10 = 0
END

IF(@W = 11 OR @Q=11 OR @B=11 OR @S=11 OR @G=11)
BEGIN
	SET @B_11 = 0
END

IF(@W = 12 OR @Q=12 OR @B=12 OR @S=12 OR @G=12)
BEGIN
	SET @B_12 = 0
END

IF(@W = 13 OR @Q=13 OR @B=13 OR @S=13 OR @G=13)
BEGIN
	SET @B_13 = 0
END

IF(@W = 14 OR @Q=14 OR @B=14 OR @S=14 OR @G=14)
BEGIN
	SET @B_14 = 0
END

IF(@W = 15 OR @Q=15 OR @B=15 OR @S=15 OR @G=15)
BEGIN
	SET @B_15 = 0
END

IF(@W = 16 OR @Q=16 OR @B=16 OR @S=16 OR @G=16)
BEGIN
	SET @B_16 = 0
END

IF(@W = 17 OR @Q=17 OR @B=17 OR @S=17 OR @G=17)
BEGIN
	set @B_17 = 0
END

IF(@W = 18 OR @Q=18 OR @B=18 OR @S=18 OR @G=18)
BEGIN
	SET @B_18 = 0
END

IF(@W = 19 OR @Q=19 OR @B=19 OR @S=19 OR @G=19)
BEGIN
	SET @B_19 = 0
END

IF(@W = 20 OR @Q=20 OR @B=20 OR @S=20 OR @G=20)
BEGIN
	SET @B_20 = 0
END

IF(@W = 21 OR @Q=21 OR @B=21 OR @S=21 OR @G=21)
BEGIN
	SET @B_21 = 0
END

IF(@W = 22 OR @Q=22 OR @B=22 OR @S=22 OR @G=22)
BEGIN
	SET @B_22 = 0
END

IF(@W % 2 =1)
BEGIN
	SET @J = @J+1
END

IF(@Q % 2 =1)
BEGIN
	SET @J = @J+1
END

IF(@B % 2 =1)
BEGIN
	SET @J = @J+1
END

IF(@S % 2 =1)
BEGIN
	SET @J = @J+1
END

IF(@G % 2 =1)
BEGIN
	SET @J = @J+1
END

IF(@W % 2 =0)
BEGIN
	SET @O = @O+1
END

IF(@Q % 2 =0)
BEGIN
	SET @O = @O+1
END

IF(@B % 2 =0)
BEGIN
	SET @O = @O+1
END

IF(@S % 2 =0)
BEGIN
	SET @O = @O+1
END

IF(@G % 2 =0)
BEGIN
	SET @O = @O+1
END

IF(@W>11)
BEGIN
	SET @D=@D + 1
END

IF(@Q>11)
BEGIN
	SET @D=@D + 1
END

IF(@B>11)
BEGIN
	SET @D=@D + 1
END

IF(@S>11)
BEGIN
	SET @D=@D + 1
END

IF(@G>11)
BEGIN
	SET @D=@D + 1
END

IF(@W<12)
BEGIN
	SET @X=@X + 1
END

IF(@Q<12)
BEGIN
	SET @X=@X + 1
END

IF(@B<12)
BEGIN
	SET @X=@X + 1
END

IF(@S<12)
BEGIN
	SET @X=@X + 1
END

IF(@G<12)
BEGIN
	SET @X=@X + 1
END

SET @H_Z =@W+@Q+@B+@S+@G

IF(@W< 08)
BEGIN
	SET @Y_0 =@Y_0 + 1
END

IF(@Q< 08)
BEGIN
	SET @Y_0 =@Y_0 + 1
END

IF(@B< 08)
BEGIN
	SET @Y_0 =@Y_0 + 1
END

IF(@S< 08)
BEGIN
	SET @Y_0 =@Y_0 + 1
END

IF(@G< 08)
BEGIN
	SET @Y_0 =@Y_0 + 1
END

IF(@W > 7 AND @W < 15)
BEGIN
	SET @Y_1=@Y_1 + 1
END

IF(@Q > 7 AND @Q < 15)
BEGIN
	SET @Y_1=@Y_1 + 1
END

IF(@B > 7 AND @B < 15)
BEGIN
	SET @Y_1=@Y_1 + 1
END

IF(@S > 7 AND @S < 15)
BEGIN
	SET @Y_1=@Y_1 + 1
END

IF(@G > 7 AND @G < 15)
BEGIN
	SET @Y_1=@Y_1 + 1
END

IF(@W > 14 AND @W < 23)
BEGIN
	SET @Y_2 =@Y_2 + 1
END

IF(@Q > 14 AND @Q < 23)
BEGIN
	SET @Y_2 =@Y_2 + 1
END

IF(@B > 14 AND @B < 23)
BEGIN
	SET @Y_2 =@Y_2 + 1
END

IF(@S > 14 AND @S < 23)
BEGIN
	SET @Y_2 =@Y_2 + 1
END

IF(@G > 14 AND @G < 23)
BEGIN
	SET @Y_2 =@Y_2 + 1
END

insert into #LotteryNumberList22X5 values (@Isuse, @WinLotterNumber ,@B_1,@B_2 ,@B_3 ,@B_4 ,@B_5,@B_6 ,@B_7 ,@B_8,@B_9,@B_10,
		@B_11,@B_12 ,@B_13 ,@B_14 ,@B_15,@B_16 ,@B_17 ,@B_18,@B_19,@B_20,@B_21,@B_22 ,@J,@O,@D,@X,
		@H_Z,@Y_0,@Y_1,@Y_2)

fetch next from @cur into @Isuse, @WinLotterNumber

end

close @cur

select * from #LotteryNumberList22X5

GO


exec P_TrendChart_22X5_HMFB